﻿/*
   Copyright 2013 Dmitry Bratus

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
*/

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace Flower.SqlServer
{
    public sealed class SqlCommandBuilder : IDisposable
    {
        private readonly SqlCommand _command;

        public SqlCommandBuilder(SqlConnection connection)
        {
            _command = new SqlCommand 
            {
                Connection = connection,
                CommandType = CommandType.StoredProcedure
            };
        }

        public SqlCommandBuilder AddParameterNull(string name)
        {
            _command.Parameters.AddWithValue(name, DBNull.Value).Direction = ParameterDirection.Input;
            return this;
        }

        public SqlCommandBuilder AddParameter(string name, int value)
        {
            _command.Parameters.Add(name, SqlDbType.Int).Value = value;
            return this;
        }
        
        public SqlCommandBuilder AddParameter(string name, long value)
        {
            _command.Parameters.Add(name, SqlDbType.BigInt).Value = value;
            return this;
        }

        public SqlCommandBuilder AddParameter(string name, string value)
        {
            _command.Parameters.Add(name, SqlDbType.NVarChar).Value = (object)value ?? DBNull.Value;
            return this;
        }

        public SqlCommandBuilder AddParameter(string name, Guid value)
        {
            _command.Parameters.Add(name, SqlDbType.UniqueIdentifier).Value = value;
            return this;
        }

        public SqlCommandBuilder AddParameter(string name, DateTime value)
        {
            if (value < SqlDateTime.MinValue.Value)
            {
                value = SqlDateTime.MinValue.Value;
            }
            else if (value > SqlDateTime.MaxValue.Value)
            {
                value = SqlDateTime.MaxValue.Value;
            }

            _command.Parameters.Add(name, SqlDbType.DateTime).Value = value;
            return this;
        }

        public SqlCommandBuilder AddParameter(string name, DateTime? value)
        {
            if (value.HasValue)
            {
                if (value < SqlDateTime.MinValue.Value)
                {
                    value = SqlDateTime.MinValue.Value;
                }
                else if (value > SqlDateTime.MaxValue.Value)
                {
                    value = SqlDateTime.MaxValue.Value;
                }
            }

            _command.Parameters.Add(name, SqlDbType.DateTime).Value = (object)value ?? DBNull.Value;
            return this;
        }

        public SqlCommandBuilder AddParameter(string name, byte[] value)
        {
            _command.Parameters.Add(name, SqlDbType.VarBinary).Value = (object)value ?? DBNull.Value;
            return this;
        }

        public SqlCommandBuilder AddParameter<T>(string name, IEnumerable<T> value)
        {
            var tbl = new DataTable();

            tbl.Columns.Add("Value", typeof(T));

            foreach (T t in value)
            {
                tbl.Rows.Add(t);
            }

            SqlParameter prm = _command.Parameters.AddWithValue(name, tbl);
            prm.SqlDbType = SqlDbType.Structured;

            if (typeof(T) == typeof(int))
            {
                prm.TypeName = "dbo.TIntSet";
            }
            else if (typeof(T) == typeof(long))
            {
                prm.TypeName = "dbo.TBigIntSet";
            }
            else if (typeof(T) == typeof(string))
            {
                prm.TypeName = "dbo.TNVarcharSet";
            }
            else if (typeof(T) == typeof(Guid))
            {
                prm.TypeName = "dbo.TUniqueIdentifierSet";
            }
            else
            {
                throw new InvalidOperationException(string.Format("Type {0} is not supported.", typeof(T).FullName));
            }

            return this;
        }

        public SqlCommandBuilder AddParameter(string name, Action<DataTableBuilder> value, string typeName)
        {
            var builder = new DataTableBuilder();
            
            value(builder);

            DataTable table = builder.Build();

            SqlParameter prm = _command.Parameters.AddWithValue(name, table);
            prm.SqlDbType = SqlDbType.Structured;
            prm.TypeName = typeName;

            return this;
        }

        public SqlCommandBuilder SetText(string text)
        {
            _command.CommandText = text;
            return this;
        }

        public int ExecuteIntFunction()
        {
            SqlParameter resultParam = _command.Parameters.Add
            (
                new SqlParameter
                {
                    ParameterName = "@Result",
                    Direction = ParameterDirection.ReturnValue,
                    SqlDbType = SqlDbType.Int
                }
            );

            _command.ExecuteNonQuery();

            return (int)resultParam.Value;
        }

        public long ExecuteLongFunction()
        {
            SqlParameter resultParam = _command.Parameters.Add
            (
                new SqlParameter
                {
                    ParameterName = "@Result",
                    Direction = ParameterDirection.ReturnValue,
                    SqlDbType = SqlDbType.BigInt
                }
            );

            _command.ExecuteNonQuery();

            return (long)resultParam.Value;
        }

        public string ExecuteStringFunction()
        {
            SqlParameter resultParam = _command.Parameters.Add
            (
                new SqlParameter
                {
                    ParameterName = "@Result",
                    Direction = ParameterDirection.ReturnValue,
                    SqlDbType = SqlDbType.NVarChar
                }
            );

            _command.ExecuteNonQuery();

            if (resultParam.Value is DBNull)
            {
                return null;
            }

            return (string)resultParam.Value;
        }

        public long ExecuteLongOutParamProcedure(string outParamName)
        {
            SqlParameter resultParam = _command.Parameters.Add
            (
                new SqlParameter
                {
                    ParameterName = "@Result",
                    Direction = ParameterDirection.ReturnValue,
                    SqlDbType = SqlDbType.Int
                }
            );
            SqlParameter outParam = _command.Parameters.Add
            (
                new SqlParameter
                {
                    ParameterName = outParamName,
                    Direction = ParameterDirection.Output,
                    SqlDbType = SqlDbType.BigInt
                }
            );

            _command.ExecuteNonQuery();

            var result = (resultParam.Value is DBNull) ? 0 : (int)resultParam.Value;

            if (result > 0)
            {
                throw new SqlCommandErrorResultException(result);
            }

            return (outParam.Value is DBNull) ? 0L : (long)outParam.Value;
        }

        public IEnumerable<T> ExecuteReader<T>(Func<SqlDataReader, T> map)
        {
            SqlParameter resultParam = _command.Parameters.Add
            (
                new SqlParameter
                {
                    ParameterName = "@Result",
                    Direction = ParameterDirection.ReturnValue,
                    SqlDbType = SqlDbType.Int
                }
            );

            using (SqlDataReader reader = _command.ExecuteReader())
            {
                while (reader.Read())
                {
                    yield return map(reader);
                }
            }

            var result = (resultParam.Value is DBNull) ? 0 : (int)resultParam.Value;

            if (result > 0)
            {
                throw new SqlCommandErrorResultException(result);
            }
        }

        public void ExecuteReaderWithAction(Action<SqlDataReader> action)
        {
            SqlParameter resultParam = _command.Parameters.Add
            (
                new SqlParameter
                {
                    ParameterName = "@Result",
                    Direction = ParameterDirection.ReturnValue,
                    SqlDbType = SqlDbType.Int
                }
            );

            using (SqlDataReader reader = _command.ExecuteReader())
            {
                while (reader.Read())
                {
                    action(reader);
                }
            }

            var result = (resultParam.Value is DBNull) ? 0 : (int)resultParam.Value;

            if (result > 0)
            {
                throw new SqlCommandErrorResultException(result);
            }
        }

        public T ExecuteSingleRow<T>(Func<SqlDataReader, T> map)
        {
            SqlParameter resultParam = _command.Parameters.Add
            (
                new SqlParameter
                {
                    ParameterName = "@Result",
                    Direction = ParameterDirection.ReturnValue,
                    SqlDbType = SqlDbType.Int
                }
            );

            T retval;

            using (SqlDataReader reader = _command.ExecuteReader())
            {
                retval = (reader.Read()) ? map(reader) : default(T);
            }

            var result = (resultParam.Value is DBNull) ? 0 : (int)resultParam.Value;

            if (result > 0)
            {
                throw new SqlCommandErrorResultException(result);
            }

            return retval;
        }

        public void Execute()
        {
            SqlParameter resultParam = _command.Parameters.Add
            (
                new SqlParameter
                {
                    ParameterName = "@Result",
                    Direction = ParameterDirection.ReturnValue,
                    SqlDbType = SqlDbType.Int
                }
            );

            _command.ExecuteNonQuery();

            var result = (resultParam.Value is DBNull) ? 0 : (int)resultParam.Value;

            if (result > 0)
            {
                throw new SqlCommandErrorResultException(result);
            }
        }

        public int ExecuteInt()
        {
            SqlParameter resultParam = _command.Parameters.Add
            (
                new SqlParameter
                {
                    ParameterName = "@Result",
                    Direction = ParameterDirection.ReturnValue,
                    SqlDbType = SqlDbType.Int
                }
            );

            object resultScalar = _command.ExecuteScalar();

            var result = (resultParam.Value is DBNull) ? 0 : (int)resultParam.Value;

            if (result > 0)
            {
                throw new SqlCommandErrorResultException(result);
            }

            return (resultScalar is DBNull) ? 0 : (int)resultScalar;
        }

        public long ExecuteLong()
        {
            SqlParameter resultParam = _command.Parameters.Add
            (
                new SqlParameter
                {
                    ParameterName = "@Result",
                    Direction = ParameterDirection.ReturnValue,
                    SqlDbType = SqlDbType.Int
                }
            );

            object resultScalar = _command.ExecuteScalar();

            var result = (resultParam.Value is DBNull) ? 0 : (int)resultParam.Value;

            if (result > 0)
            {
                throw new SqlCommandErrorResultException(result);
            }

            return (resultScalar is DBNull) ? 0L : (long)resultScalar;
        }

        public string ExecuteString()
        {
            SqlParameter resultParam = _command.Parameters.Add
            (
                new SqlParameter
                {
                    ParameterName = "@Result",
                    Direction = ParameterDirection.ReturnValue,
                    SqlDbType = SqlDbType.Int
                }
            );

            object resultScalar = _command.ExecuteScalar();

            var result = (resultParam.Value is DBNull) ? 0 : (int)resultParam.Value;

            if (result > 0)
            {
                throw new SqlCommandErrorResultException(result);
            }

            return (resultScalar is DBNull) ? null : (string)resultScalar;
        }

        public void Dispose()
        {
            _command.Dispose();
        }
    }
}
